package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.dao.FinReportDAO;
import edu.cmu.cs.webapp.ioms.models.IOMSPackage;
import edu.cmu.cs.webapp.ioms.models.Product;

public class FinReportDAOImpl extends DAO implements FinReportDAO{

	//return the total cost for a particular month.
	@Override
	public double getMonthlyCost(int Year, int Month) throws DataAccessException{
		// TODO Auto-generated method stub
		Connection con = null;
		
		double result = 0;
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select sum(ing_std_quantity*unit_cost) sum from ( ( ( po join item using(order_id) ) join product using(prod_id) ) join formula using(prod_id) ) join ingredient using(ing_id) where year(order_date)=" + Year + " and month(order_date)="+ Month;
        	ResultSet rs = stmt.executeQuery(sql);
        	if(rs.next())
        		result = rs.getDouble("sum");
        	
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return result;
	}

	//return the total revenue(price) of a specific month
	@Override
	public double getMonthlyRevenue(int Year, int Month) throws DataAccessException{
		// TODO Auto-generated method stub
		Connection con = null;
		
		double result = 0;
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select sum(product.price*item.quantity) sum from ( po join item using(order_id) ) join product using(prod_id) where year(order_date)=" + Year + " and month(order_date)="+ Month;
        	ResultSet rs = stmt.executeQuery(sql);
        	if(rs.next())
        		result = rs.getDouble("sum");
        	
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return result;
	}
	
	
	//PLEASE DO NOT CHANGE THIS METHOD.
	@Override
	public double[] getCostByMonth(int fromYear, int fromMonth, int toYear,
			int toMonth) throws DataAccessException{
		int numMonth = ((toYear - fromYear) * 12 + toMonth) - fromMonth + 1;
		double[] costs=new double[numMonth];
		for(int i=0;i<numMonth;i++){
			costs[i]=getMonthlyCost(getDate(fromYear,fromMonth,i)[0],
					getDate(fromYear,fromMonth,i)[1]);
		}
		
		return costs;
	}

	//PLEASE DO NOT CHANGE THIS METHOD.
	@Override
	public double[] getRevenueByMonth(int fromYear, int fromMonth, int toYear,
			int toMonth) throws DataAccessException{
		
		int numMonth = ((toYear - fromYear) * 12 + toMonth) - fromMonth + 1;
		double[] revenues=new double[numMonth];
		for(int i=0;i<numMonth;i++){
			revenues[i]=getMonthlyRevenue(getDate(fromYear,fromMonth,i)[0],
					getDate(fromYear,fromMonth,i)[1]);
		}
		
		return revenues;
	}

	
	//PLEASE DO NOT CHANGE THIS METHOD.
	private int[] getDate(int year, int month, int offset) {
		int[] result = new int[2];
		int temp = month + offset;

		if (temp % 12 == 0)
			month = 12;
		else {
			month = temp % 12;
			year += temp / 12;
		}
		result[0] = year;
		result[1] = month;
		return result;
	}

	

}
